Welcome to "Accessing Your Data Warehouse
Using IBM Cognos." After watching this video, you will be able
to: Describe the steps involved in accessing a
database or data warehouse from Cognos Analytics. Create an interactive dashboard using data
in the data warehouse, and Add multiple visualizations to the dashboard. In addition to files and static data sets,
you can use Cognos Analytics to access a variety of databases and data warehouses both on the
cloud and in your enterprise, including IBM Db2 Warehouse, Amazon Redshift, Oracle,
Microsoft SQL Server, MongoDB, MySQL, PostgreSQL, Snowflake, and others. First, identify the source databases, including
schemas and tables, and obtain their connection credentials. Next, within Cognos Analytics, you'll create
data server connections to the selected databases. And then, for each connection, create data
modules for the selected tables. Next, create a dashboard and add the data modules as sources. And then drag columns from the data module onto the
dashboard canvas. Let's see these five steps in action. For
this demo, you will use an “IBM Db2 on Cloud” database as the source data warehouse. First, you need to obtain the service credentials
to connect to the database from the Db2 instance page by clicking "New Credential." Now you need to name the service credential,
set the role to Manager, and click Add to add the service credential.
Scrolling down, you can see the service credential you just created displayed within the “Service
credentials” list. Click the credential drop-down arrow to view
the credential's details. Here's a snippet of the service credential
details. Copy the username, password, and the JDBC URL. You will need to replace the placeholder values
for the username and password in the JDBC URL with actual values. Remember to remove the angle brackets. Next, let's identify and preview the data,
that is, the schema and tables, that you need for the Cognos dashboard. Under Manage, click Go to UI. This action
opens the page where you can view, edit, and administer the Db2 instance. Within the Db2 UI, click the data icon, select
the schema, and select the table you want to use for your dashboard in Cognos Analytics. Select the Customer Loyalty table. Here, you are previewing the Customer Loyalty
table in the LFN96733 schema. Viewing your table provides ideas about the
data attributes you can visualize. Now that you have the database credentials
and identified the data you want to visualize, go to the Cognos Analytics web page, click
the hamburger menu, and then Manage. You'll see several management options, but
your task is to click Data server connections to connect with a data server. Click the plus icon to view the data warehouse
options that you can connect to using Cognos. Next, click IBM Db2. Click the option to connect anonymously and
paste the JDBC connection string you copied from the Db2 instance administration page. Replace the username and password with your
Db2 service credentials. Click Test to ensure you have a successful
data connection. If the test was successful, you'll see Success
with a green checkmark icon. You can then name the connection. Here, the connection is named MyDB2. Click Save. Next, you'll load the schema. Go to the Schema tab and click the three dots
next to the schema you want to load. Then, click Load metadata. This action takes a few seconds while Cognos
fetches and loads the metadata from the Db2 instance. Once the metadata is loaded, you'll see a
green checkmark next to the schema name. Now that you have connected to the DB2 instance,
you create a new data module that will link to the source data. This action opens a page
where you can select the data source. Click the Data servers icon in the left pane.
Then, click MyDB2— which is the connection you previously created. Here, click the schema you want to use and
click OK. When the new module opens, click the table
name. Cognos will open the table and you can view the data. Click the File icon to save the table. Give the data module an applicable name. Here the data module is named "MyCustomerLoyaltyModule”
and is saved within the My Content folder. Click save. Now that the data module is created, you will
use it to create your dashboard. On the IBM Cognos Analytics menu, click New
and then click Dashboard. You can select the Tabbed or Infographic layout,
with multiple template options in each. For this demo session, you click this template
and then click Create. Click Select a source to choose the data module
to use for the dashboard. In the dashboard window, click the “My Customer
Loyalty” data module in the Name column as your source, and then click Add. In the left pane of the My Customer Loyalty
data module, drag the attributes that you want to see onto the dashboard. In this example,
the Quantity Sold is selected. You can see the total quantity sold, along
with the title, which is the field label by default. You can double-click the label to change or
format the text. You can also adjust the attribute’s length,
width, and position based on your design requirements. Next, drag the attributes into the tabbed
area and render them as a bar plot." The attributes are rendered as a bar plot
showing the sale of each product line over the years. You can adjust the size and position of the
tab, and you can add a title and format it like the Quantity Sold attribute. For the third tab, let's drag Country below
Quantity Sold to visualize regional sales. The world map appears. You can adjust the
size of the region based on your needs. Let's add some attributes to the map. You want to add Quantity Sold so that you can visualize
regional sales. Now, when you click a particular region, you
can see the bar graph, and the quantity sold is updated as per the selected region. Here is a glimpse of the finished dashboard. Remember to save your completed dashboard
with an appropriate link. Cognos makes it easy for you to share the finished dashboard
as a viewable link. In this video, you learned that: You can create dashboards and reports in Cognos
Analytics using various data sources, including flat files, databases, and data warehouses. Accessing a database in Cognos requires a
data server connection and a data module. A data server connection establishes a connection
to the database and a data module is a container, or link, to specific tables and database
objects. You can add a data module to a dashboard just
like any other source and use its fields and columns to create visualizations. Cognos Analytics offers a variety of templates
and visualization options to create interactive dashboards using data from data warehouses.